
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 06/10/2014 07:23:42
-- Generated from EDMX file: D:\Projects\SoccerEvaluator\SoccerEvaluator.WebSite\SoccerEvaluatorModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [SoccerEvaluator];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_GroupTemplate]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Templates] DROP CONSTRAINT [FK_GroupTemplate];
GO
IF OBJECT_ID(N'[dbo].[FK_TemplateTemplateScore]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TemplateScores] DROP CONSTRAINT [FK_TemplateTemplateScore];
GO
IF OBJECT_ID(N'[dbo].[FK_SystemUserUserScore]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserScores] DROP CONSTRAINT [FK_SystemUserUserScore];
GO
IF OBJECT_ID(N'[dbo].[FK_TemplateUserScore]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserScores] DROP CONSTRAINT [FK_TemplateUserScore];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Groups]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Groups];
GO
IF OBJECT_ID(N'[dbo].[Templates]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Templates];
GO
IF OBJECT_ID(N'[dbo].[TemplateScores]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TemplateScores];
GO
IF OBJECT_ID(N'[dbo].[SystemUsers]', 'U') IS NOT NULL
    DROP TABLE [dbo].[SystemUsers];
GO
IF OBJECT_ID(N'[dbo].[UserScores]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UserScores];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Groups'
CREATE TABLE [dbo].[Groups] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Enabled] bit  NOT NULL,
    [Logo] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Templates'
CREATE TABLE [dbo].[Templates] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [CreationDate] datetime  NOT NULL,
    [CatchScore] int  NOT NULL,
    [ExactScore] int  NOT NULL,
    [Image] nvarchar(max)  NOT NULL,
    [StartDate] datetime  NOT NULL,
    [EndDate] datetime  NOT NULL,
    [Mode] int  NOT NULL,
    [GroupId] int  NOT NULL
);
GO

-- Creating table 'TemplateScores'
CREATE TABLE [dbo].[TemplateScores] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Index] int  NOT NULL,
    [Date] datetime  NOT NULL,
    [Local] nvarchar(max)  NOT NULL,
    [LocalGoals] int  NOT NULL,
    [Visitor] nvarchar(max)  NOT NULL,
    [VisitorGoals] int  NOT NULL,
    [TemplateId] int  NOT NULL,
    [Image] nvarchar(max)  NOT NULL,
    [Location] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'SystemUsers'
CREATE TABLE [dbo].[SystemUsers] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [UserName] nvarchar(max)  NOT NULL,
    [CreationDate] datetime  NOT NULL,
    [LastEntry] datetime  NOT NULL,
    [Email] nvarchar(max)  NOT NULL,
    [Password] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'UserScores'
CREATE TABLE [dbo].[UserScores] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Index] int  NOT NULL,
    [LocalGoals] int  NOT NULL,
    [VisitorGoals] int  NOT NULL,
    [Score] int  NOT NULL,
    [SystemUserId] int  NOT NULL,
    [TemplateId] int  NOT NULL,
    [Date] datetime  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'Groups'
ALTER TABLE [dbo].[Groups]
ADD CONSTRAINT [PK_Groups]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Templates'
ALTER TABLE [dbo].[Templates]
ADD CONSTRAINT [PK_Templates]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'TemplateScores'
ALTER TABLE [dbo].[TemplateScores]
ADD CONSTRAINT [PK_TemplateScores]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'SystemUsers'
ALTER TABLE [dbo].[SystemUsers]
ADD CONSTRAINT [PK_SystemUsers]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'UserScores'
ALTER TABLE [dbo].[UserScores]
ADD CONSTRAINT [PK_UserScores]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [GroupId] in table 'Templates'
ALTER TABLE [dbo].[Templates]
ADD CONSTRAINT [FK_GroupTemplate]
    FOREIGN KEY ([GroupId])
    REFERENCES [dbo].[Groups]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_GroupTemplate'
CREATE INDEX [IX_FK_GroupTemplate]
ON [dbo].[Templates]
    ([GroupId]);
GO

-- Creating foreign key on [TemplateId] in table 'TemplateScores'
ALTER TABLE [dbo].[TemplateScores]
ADD CONSTRAINT [FK_TemplateTemplateScore]
    FOREIGN KEY ([TemplateId])
    REFERENCES [dbo].[Templates]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TemplateTemplateScore'
CREATE INDEX [IX_FK_TemplateTemplateScore]
ON [dbo].[TemplateScores]
    ([TemplateId]);
GO

-- Creating foreign key on [SystemUserId] in table 'UserScores'
ALTER TABLE [dbo].[UserScores]
ADD CONSTRAINT [FK_SystemUserUserScore]
    FOREIGN KEY ([SystemUserId])
    REFERENCES [dbo].[SystemUsers]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_SystemUserUserScore'
CREATE INDEX [IX_FK_SystemUserUserScore]
ON [dbo].[UserScores]
    ([SystemUserId]);
GO

-- Creating foreign key on [TemplateId] in table 'UserScores'
ALTER TABLE [dbo].[UserScores]
ADD CONSTRAINT [FK_TemplateUserScore]
    FOREIGN KEY ([TemplateId])
    REFERENCES [dbo].[Templates]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TemplateUserScore'
CREATE INDEX [IX_FK_TemplateUserScore]
ON [dbo].[UserScores]
    ([TemplateId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------